import dayjs from 'dayjs';
import path from 'path';
import sqlite3 from 'sqlite3';
import colors from 'colors-console';
import Log from '../utils/log';
import { config } from '../config';

class SQLiteDB {
  db: sqlite3.Database;
  databaseName: string;
  constructor(databaseName: string) {
    // 使用 sqlite3.verbose() 方法来启用详细的日志
    sqlite3.verbose();
    this.databaseName = databaseName;

    // 使用 sqlite3.Database 创建数据库连接
    this.db = new sqlite3.Database(databaseName, (err) => {
      if (err) {
        Log.error(`无法连接到数据库: ${err.message}`);
      } else {
        Log.info(`成功连接到数据库: ${databaseName}`);
        this.countTables();
      }
    });
  }

  /**
   * 查询已经创建的表
   */
  async countTables() {
    try {
      const query = `SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';`;
      const res = (await this.executeQuery(query, [])) as { name: string }[];
      let tables = res.map((row) => row.name);
      Log.info(`已创建的表数量: ${tables.length}`);
      Log.info(`表: ${JSON.stringify(tables)}`);
    } catch (error) {
      Log.error(`查询已创建的表失败: ${error}`);
    }
  }

  /**
   * 创建表
   * @param tableName
   * @param columns
   * @param cb
   * @example
   * const columns = [
   *   { name: 'id', type: 'INTEGER PRIMARY KEY AUTOINCREMENT' },
   *   { name: 'name', type: 'TEXT' },
   *   { name: 'age', type: 'INTEGER' },
   * ];
   * let result = await dbServe.createTable('test', columns);
   * if (result) {
   *   res.sendSuccess('表创建成功');
   * } else {
   *   res.sendError(`创建表失败: ${result}`);
   * }
   */
  createTable(tableName: string, columns: { name: string; type: string }[]): Promise<boolean> {
    return new Promise((resolve, reject) => {
      let columnDefinitions = columns.map((column) => `${column.name} ${column.type}`).join(', ');
      columnDefinitions = `${columnDefinitions}, created_at TEXT NOT NULL, updated_at TEXT NOT NULL`;
      const query = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefinitions})`;

      this.db.run(query, (err) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(true); // 成功时解析 Promise
        }
      });
    });
  }

  /**
   * 删除表
   * @param tableName
   * @param cb
   * @example
   * let result = await dbServe.deleteTable('test');
   *   if (result) {
   *     res.sendSuccess('表删除成功');
   *   } else {
   *     res.sendError(`删除表失败: ${result}`);
   * }
   */
  deleteTable(tableName: string): Promise<boolean> {
    return new Promise((resolve, reject) => {
      const query = `DROP TABLE IF EXISTS ${tableName}`;
      this.db.run(query, (err) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(true); // 成功时解析 Promise
        }
      });
    });
  }

  /**
   * 插入数据
   * @param tableName
   * @param data
   * @param cb
   * @example
   * const data = { name: 'John Doe', age: 25 };
   * let result = await dbServe.insertData('test', data);
   * if (result) {
   *   res.sendSuccess('数据插入成功');
   * } else {
   *   res.sendError(`插入数据失败: ${result}`);
   * }
   */
  insertData(tableName: string, data: { [s: string]: unknown } | ArrayLike<unknown>): Promise<boolean> {
    return new Promise((resolve, reject) => {
      let columns = Object.keys(data).join(', ');
      let placeholders = Object.keys(data)
        .map(() => '?')
        .join(', ');
      const values = Object.values(data);

      // 加入自动更新时间
      columns = `${columns}, created_at, updated_at`;
      placeholders = `${placeholders}, ?, ?`;
      // 获取当前时间
      const now = dayjs().format('YYYY-MM-DD HH:mm:ss');
      values.push(now, now);

      const query = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
      this.db.run(query, values, (err) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(true); // 成功时解析 Promise
        }
      });
    });
  }

  /**
   * 更新数据
   * @param tableName
   * @param data
   * @param condition
   * @param cb
   * @example
   * const newData = { age: 30 };
   * const condition = 'name = "John Doe"';
   * let result = await dbServe.updateData('test', newData, condition);
   * if (result) {
   *   res.sendSuccess('数据更新成功');
   * } else {
   *   res.sendError(`更新数据失败: ${result}`);
   * }
   */
  updateData(tableName: string, data: { [s: string]: unknown } | ArrayLike<unknown>, condition: any): Promise<boolean> {
    return new Promise((resolve, reject) => {
      let setClause = Object.keys(data)
        .map((column) => `${column} = ?`)
        .join(', ');
      let values = Object.values(data);

      // 加入自动更新时间
      setClause = `${setClause}, updated_at = ?`;
      // 获取当前时间
      const now = dayjs().format('YYYY-MM-DD HH:mm:ss');
      values.push(now);

      const query = `UPDATE ${tableName} SET ${setClause} WHERE ${condition}`;
      this.db.run(query, values, (err) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(true); // 成功时解析 Promise
        }
      });
    });
  }

  /**
   * 删除数据
   * @param tableName
   * @param condition
   * @param cb
   * @example
   * const deleteCondition = 'age >= 30';
   * let result = await dbServe.deleteData('test', deleteCondition);
   * if (result) {
   *   res.sendSuccess('数据删除成功');
   * } else {
   *   res.sendError(`删除数据失败: ${result}`);
   * }
   */
  deleteData(tableName: string, condition: string): Promise<boolean> {
    return new Promise((resolve, reject) => {
      const query = `DELETE FROM ${tableName} WHERE ${condition}`;
      this.db.run(query, (err) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(true); // 成功时解析 Promise
        }
      });
    });
  }

  /**
   * 查询数据 执行自定义SQL查询
   * @param query
   * @param params
   * @param callback
   * @example
   * const query = 'SELECT * FROM users WHERE age > ?';
   * const params = [30];
   * dbServe.executeQuery(query, params, rows => {
   *   console.log(rows);
   * });
   */
  executeQuery<T>(query: string, params: any[]): Promise<T[] | null> {
    return new Promise((resolve, reject) => {
      this.db.all<T>(query, params, (err, rows) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(rows); // 成功时解析 Promise
        }
      });
    });
  }

  /**
   * 获取分页数据
   * @param tableName
   * @param page
   * @param pageSize
   * @param condition // const condition = "column_name = 'value'";
   * @returns {Promise<unknown>}
   * @example
   * const page = 1;
   * const pageSize = 10;
   * const condition = 'age >= 30';
   * let result = await dbServe.getPagedData('test', page, pageSize, condition);
   * if (result) {
   *   res.sendSuccess('分页查询成功' + JSON.stringify(result));
   * } else {
   *   res.sendError(`分页查询失败: ${result}`);
   * }
   */
  getPagedData(
    tableName: string,
    page: number,
    pageSize: number,
    condition: string,
  ): Promise<{ data: unknown[]; total: number; totalPages: number }> {
    return new Promise((resolve, reject) => {
      const offset = (page - 1) * pageSize;
      const countQuery = `SELECT COUNT(*) as total FROM ${tableName} WHERE ${condition}`;
      const dataQuery = `SELECT * FROM ${tableName} WHERE ${condition} LIMIT ? OFFSET ?`;

      this.db.serialize(() => {
        this.db.get(countQuery, (err, row) => {
          if (err) {
            reject(err); // 发生错误时拒绝 Promise
          } else {
            const total = (row as any).total;
            this.db.all(dataQuery, [pageSize, offset], (err, rows) => {
              if (err) {
                reject(err); // 发生错误时拒绝 Promise
              } else {
                const totalPages = Math.ceil(total / pageSize);
                resolve({ data: rows, total, totalPages }); // 成功时解析 Promise
              }
            });
          }
        });
      });
    });
  }

  /**
   * 关闭数据库连接
   */
  closeConnection(): Promise<boolean> {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) {
          reject(err); // 发生错误时拒绝 Promise
        } else {
          resolve(true); // 成功时解析 Promise
        }
      });
    });
  }
}

const dbServe = new SQLiteDB(path.join(config.PROJECT_DIR, './src/db/sqlite.sqlite3'));
export default dbServe;
// 基于该博主提供的类，进行了二次封装和优化，原文链接：https://blog.csdn.net/No_Name_Cao_Ni_Mei/article/details/132869348
